package com.ergwang.dao;

import com.ergwang.Model.admin.bo.ChangeOrderBO;
import com.ergwang.Model.admin.bo.OrdersByPageBO;
import com.ergwang.Model.admin.vo.OrderGetInfoVO;
import com.ergwang.Model.admin.vo.OrderGetInfo_SpecVO;
import com.ergwang.Model.admin.vo.PageOrderInfoVO;
import com.ergwang.utils.DruidUtils;
import com.mysql.jdbc.StringUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * @author：ergwang
 * @Date: 2020/5/31
 * @Time: 11:47
 */
public class OrderDaoImpl implements OrderDao{

    /**
     * 分页查询的sql语句
     * @param ordersByPageBO
     * @return
     */
    public List<PageOrderInfoVO> ordersByPage(OrdersByPageBO ordersByPageBO) {
        Map<String,Object> result = getDynamicSql(ordersByPageBO);
        String prefix_sql = "select id,userId,goodsDetailID,goods,spec,num as goodsNum,amount,stateId," +
                "nickname,name,address,phone ";
        String sql = (String) result.get("sql");
        List<Object> params = (List<Object>) result.get("params");
        params.add(ordersByPageBO.getPagesize());
        params.add((ordersByPageBO.getCurrentPage() - 1) * ordersByPageBO.getPagesize());
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<PageOrderInfoVO> query = null;
        try {
            query = runner.query(prefix_sql + sql + " limit ? offset ? ",
                    new BeanListHandler<PageOrderInfoVO>(PageOrderInfoVO.class),
                    params.toArray());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return query;
    }

    /**
     * select count(key) from orders where ...
     * @param ordersByPageBO
     * @return
     */
    public int getTotalCounts(OrdersByPageBO ordersByPageBO) {
        Map<String, Object> result = getDynamicSql(ordersByPageBO);
        String sql = (String) result.get("sql");
        List<Object> params = (List<Object>) result.get("params");
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        String prefix_sql = "select count(id) ";
        Long query = null;
        try {
            query = runner.query(prefix_sql + sql, new ScalarHandler<Long>(), params.toArray());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return query.intValue();
    }

    /**
     * 删除订单
     * @param id
     */
    public void deleteOrder(String id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("delete from orders where id = ?",id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    public OrderGetInfoVO OrderGetInfo(Integer id) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        OrderGetInfoVO query = null;
        try {
            query = runner.query("select id,amount,num,goodsId as goodsDetailId, " +
                            "stateId as state,goods,stateId as curState, specId as curSpec " +
                            " from orders where id = ?",
                    new BeanHandler<OrderGetInfoVO>(OrderGetInfoVO.class),
                    id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return query;
    }

    public List<OrderGetInfo_SpecVO> OrderGetInfo_Spec(Integer goodsDetailId) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        List<OrderGetInfo_SpecVO> query = null;
        try {
            query = runner.query("select id,specName,unitPrice from spec where goodsId = ? ",
                    new BeanListHandler<OrderGetInfo_SpecVO>(OrderGetInfo_SpecVO.class),
                    goodsDetailId);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return query;
    }

    /**
     * 修改订单，暂时只有修改订单状态
     * @param changeOrderBO
     */
    public void changeOrder(ChangeOrderBO changeOrderBO) {
        QueryRunner runner = new QueryRunner(DruidUtils.getDataSource());
        try {
            runner.update("update orders set stateId = ? where id = ?",
                    changeOrderBO.getState(), changeOrderBO.getId());
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
    }

    private Map<String, Object> getDynamicSql(OrdersByPageBO ordersByPageBO) {
        String base = " from orders where 1 =1 ";

        List<Object> list = new ArrayList<Object>();
        if (ordersByPageBO.getState() != -1){
            base = base + " and stateId = ? ";
            list.add(ordersByPageBO.getState());
        }
        if (!StringUtils.isNullOrEmpty(ordersByPageBO.getMoneyLimit1())){
            base = base + " and amount <= ? ";
            list.add(Double.parseDouble(ordersByPageBO.getMoneyLimit1()));
        }
        if (!StringUtils.isNullOrEmpty(ordersByPageBO.getMoneyLimit2())){
            base = base + " and amount >= ? ";
            list.add(Double.parseDouble(ordersByPageBO.getMoneyLimit2()));
        }
        if (!StringUtils.isNullOrEmpty(ordersByPageBO.getGoods())){
            base = base + " and goods like ? ";
            list.add("%"+ ordersByPageBO.getGoods() +"%");
        }
        if (!StringUtils.isNullOrEmpty(ordersByPageBO.getAddress())){
            base = base + "and address like ?";
            list.add("%"+ ordersByPageBO.getAddress() +"%");
        }
        if (!StringUtils.isNullOrEmpty(ordersByPageBO.getName())){
            base = base + "and name like ?";
            list.add("%"+ ordersByPageBO.getName() +"%");
        }
        if (!StringUtils.isNullOrEmpty(ordersByPageBO.getId())){
            base = base + "and id = ?";
            list.add(Integer.parseInt(ordersByPageBO.getId()));
        }
        Map<String,Object> map = new HashMap<String, Object>();
        map.put("sql",base);
        map.put("params",list);
        return map;
    }
}
